"""Add role and permission tables

Revision ID: add_role_and_permission_tables
Revises: f3754d370ede
Create Date: 2025-08-02 17:10:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision = 'add_role_and_permission_tables'
down_revision = 'f3754d370ede'
branch_labels = None
depends_on = None


def upgrade():
    # 创建权限表
    op.create_table('permission',
        sa.Column('id', sa.Integer(), nullable=False, comment='模型ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='权限名称'),
        sa.Column('code', sa.String(length=100), nullable=False, comment='权限代码'),
        sa.Column('resource', sa.String(length=50), nullable=False, comment='资源'),
        sa.Column('action', sa.String(length=50), nullable=False, comment='操作'),
        sa.Column('description', sa.Text(), nullable=True, comment='权限描述'),
        sa.Column('create_time', sa.DateTime(), nullable=True, comment='创建时间'),
        sa.Column('update_time', sa.DateTime(), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('code'),
        mysql_collate='utf8mb4_unicode_ci',
        mysql_default_charset='utf8mb4'
    )
    
    # 创建角色表
    op.create_table('role',
        sa.Column('id', sa.Integer(), nullable=False, comment='模型ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='角色名称'),
        sa.Column('code', sa.String(length=100), nullable=False, comment='角色代码'),
        sa.Column('description', sa.Text(), nullable=True, comment='角色描述'),
        sa.Column('is_active', sa.Boolean(), nullable=True, comment='是否激活'),
        sa.Column('create_time', sa.DateTime(), nullable=True, comment='创建时间'),
        sa.Column('update_time', sa.DateTime(), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('code'),
        mysql_collate='utf8mb4_unicode_ci',
        mysql_default_charset='utf8mb4'
    )
    
    # 创建角色权限关联表
    op.create_table('role_permission',
        sa.Column('id', sa.Integer(), nullable=False, comment='模型ID'),
        sa.Column('role_id', sa.Integer(), nullable=False, comment='角色ID'),
        sa.Column('permission_id', sa.Integer(), nullable=False, comment='权限ID'),
        sa.Column('create_time', sa.DateTime(), nullable=True, comment='创建时间'),
        sa.Column('update_time', sa.DateTime(), nullable=True, comment='更新时间'),
        sa.ForeignKeyConstraint(['permission_id'], ['permission.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['role_id'], ['role.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        mysql_collate='utf8mb4_unicode_ci',
        mysql_default_charset='utf8mb4'
    )
    
    # 创建用户角色关联表
    op.create_table('user_role',
        sa.Column('id', sa.Integer(), nullable=False, comment='模型ID'),
        sa.Column('user_id', sa.Integer(), nullable=False, comment='用户ID'),
        sa.Column('role_id', sa.Integer(), nullable=False, comment='角色ID'),
        sa.Column('create_time', sa.DateTime(), nullable=True, comment='创建时间'),
        sa.Column('update_time', sa.DateTime(), nullable=True, comment='更新时间'),
        sa.ForeignKeyConstraint(['role_id'], ['role.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
        mysql_collate='utf8mb4_unicode_ci',
        mysql_default_charset='utf8mb4'
    )


def downgrade():
    # 删除用户角色关联表
    op.drop_table('user_role')
    
    # 删除角色权限关联表
    op.drop_table('role_permission')
    
    # 删除角色表
    op.drop_table('role')
    
    # 删除权限表
    op.drop_table('permission')
